package com.glureau.aib.server.db.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.glureau.aib.shared.equipe.PionInfo;
import com.glureau.aib.shared.equipe.PionType;

public class DB_Pion extends DB_Conn {

	private String[] m_results;

	/**
	 * constructor
	 */
	public DB_Pion() {
	}

	public void addPion(int index, String name, PionType type, String aiRef,
			String parameters, int userId) {
		// SQL syntax used for a DATETIME : 'YYYY-MM-DD HH:MM:SS'
		Calendar c = Calendar.getInstance();
		String sqlTimestamp = c.get(Calendar.YEAR) + "-"
				+ (c.get(Calendar.MONTH) + 1) + "-"
				+ c.get(Calendar.DAY_OF_MONTH) + " "
				+ c.get(Calendar.HOUR_OF_DAY) + ":" + c.get(Calendar.MINUTE)
				+ ":" + c.get(Calendar.SECOND);
		String query = "INSERT INTO pion (name, type, aiRef, parameters, date, numero, userId) VALUES('"
				+ name
				+ "', '"
				+ type.toString()
				+ "', '"
				+ aiRef
				+ "', '"
				+ parameters
				+ "'"
				+ ", '"
				+ sqlTimestamp
				+ "'"
				+ ", '"
				+ index + "', '" + userId + "');";
		System.out.println(query);
		try {
			Connection connection = this.getConn();
			Statement insert = connection.createStatement();
			insert.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<PionInfo> getPions(String sessionId) {
		try {
			ArrayList<PionInfo> infos = new ArrayList<PionInfo>();

			Connection connection = this.getConn();
			Statement select = connection.createStatement();

			String query = "SELECT pionCount FROM user INNER JOIN connection ON user.id=connection.userId WHERE connection.sessionId='"
					+ sessionId + "';";
			System.out.println(query);
			ResultSet result = select.executeQuery(query);
			int pionCount = 0;
			if (result.next()) {
				pionCount = result.getInt(1);
			}
			System.err.println("Count : " + pionCount);
			for (int i = 0; i < pionCount; i++) {
				infos.add(getPion(sessionId, i, select));
			}
			connection.close();
			return infos;
		} catch (Exception e) {
			e.printStackTrace();
			System.exit(0);
		}
		return null;
	}

	private PionInfo getPion(String sessionId, int index, Statement select) throws SQLException {
		String query = "SELECT numero, name, type, aiRef, parameters, pion.date FROM pion " +
				"INNER JOIN connection ON pion.userId=connection.userId WHERE connection.sessionId='"
					+ sessionId + "' AND numero='"
				+ index + "' ORDER BY pion.date DESC;";
		System.out.println(query);
		ResultSet result = select.executeQuery(query);

		PionInfo pion = null;
		if (result.next()) {
			pion = new PionInfo();
			pion.setNumero(result.getInt(1));
			pion.setName(result.getString(2));
			pion.setType(PionType.valueOf(result.getString(3)));
			pion.setAiRef(result.getString(4));
			pion.setParameters(result.getString(5));
		}
		return pion;
	}

	/**
	 * method to test query
	 * 
	 * This is an example query, not used in rpc This is run in
	 * "RunMeToTestQuery.java" as a java application
	 */
	public void printAllPions() {

		String query = "SELECT name, type, aiRef, parametersRef, date FROM pion;";
		System.out.println(query);

		try {
			Connection connection = this.getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);

			// get count so I can set my array to the right length
			int rsSize = getResultSetSize(result);
			m_results = new String[rsSize];

			int i = 0;
			while (result.next()) {

				m_results[i] = result.getString(1);

				// this is a great way to see what is going on in the Eclipse
				// Console or using a JVM
				System.out.println("------------------------------");
				System.out.println("Name:\t" + result.getString(1));
				System.out.println("Type:\t" + result.getString(2));
				System.out.println("AI:\t" + result.getString(3));
				System.out.println("Param:\t" + result.getString(4));

				i++;
			}
			System.out.println("------------------------------");
			connection.close();
		} catch (Exception e) {
			// debug out output this way
			e.printStackTrace();

		}

	}

	public void savePion(String sessionId, PionInfo pionInfo) {
		System.out.println("Try to save a pion...");
		int userId = new DB_Login().getUserIdFromSession(sessionId);
		System.out.println("For the user " + userId);
		addPion(pionInfo.getNumero(), pionInfo.getName(), pionInfo.getType(), pionInfo.getAiRef(), pionInfo.getParameters(), userId);
	}

	public PionInfo getPion(String sessionId, int numero) {
		PionInfo pion = null;
		try {
			Connection connection = this.getConn();
			Statement select = connection.createStatement();
			String query = "SELECT numero, name, type, aiRef, parameters, pion.date FROM pion "
					+ "INNER JOIN connection ON pion.userId=connection.userId WHERE connection.sessionId='"
					+ sessionId + "' AND numero='" + numero + "' ORDER BY pion.date DESC;";
			System.out.println(query);
			ResultSet result = select.executeQuery(query);

			if (result.next()) {
				pion = new PionInfo();
				pion.setNumero(result.getInt(1));
				pion.setName(result.getString(2));
				pion.setType(PionType.valueOf(result.getString(3)));
				pion.setAiRef(result.getString(4));
				pion.setParameters(result.getString(5));
				System.out.println("Extract Pion : " + pion.toString());
			}
		} catch (Exception e) {
			// debug out output this way
			e.printStackTrace();

		}
		return pion;
	}
}
